package br.ceut.especializacao.poo.fornecedores;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import br.ceut.especializacao.poo.livros.Livro;
import br.ceut.especializacao.poo.livros.RepositorioLivros;
import br.ceut.especializacao.poo.livros.RepositorioLivrosArray;
import br.ceut.especializacao.poo.util.PersistenceMechanismException;
import br.ceut.especializacao.poo.util.PersistenceMechanismRDBMS;
import br.ceut.especializacao.poo.util.RepositorioException;

public class RepositorioFornecedoresBDR implements RepositorioFornecedores {

    private static RepositorioFornecedoresBDR instance;
    private static RepositorioEnderecosBDR instanceEndereco;
    private PersistenceMechanismRDBMS pm;
	
    private RepositorioFornecedoresBDR() {
        try {
            pm = PersistenceMechanismRDBMS.getInstance();
            pm.connect();
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public synchronized static RepositorioFornecedoresBDR getInstance() {
        if (instance == null) {
            instance = new RepositorioFornecedoresBDR();
        }
        return instance;
    }

    public void inserir(Fornecedor fornecedor) throws RepositorioException {
        try {
            Statement statement = (Statement) pm.getCommunicationChannel();
            int idEndereco = instanceEndereco.inserir(fornecedor.getEndereco());
            statement.executeUpdate("INSERT INTO FORNECEDOR VALUES (" + idEndereco + "," +
            		" '" + fornecedor.getNome() + "'," +
            		" '" + fornecedor.getEmail() + "'," +
            		" '" + fornecedor.getTelefone() + "')");
        } catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
        } catch (SQLException e) {
            throw new RepositorioException(e);
	    } finally {
		    try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
        }
    }

	public void remover(int idFornecedor) throws FornecedorNaoEncontradoException, RepositorioException {
        try{
            Statement statement = (Statement) pm.getCommunicationChannel();
		    int i = statement.executeUpdate("DELETE FROM FORNECEDOR WHERE idFornecedor='" + idFornecedor + "'");
            if (i == 0) {
            	throw new FornecedorNaoEncontradoException(idFornecedor);
            }
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
		} catch(SQLException e){
            throw new RepositorioException(e);            
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
	}

	public Fornecedor procurar(int idFornecedor) throws FornecedorNaoEncontradoException, RepositorioException {
		Fornecedor fornecedor = null;
        try {
            Statement statement = (Statement) pm.getCommunicationChannel();
            ResultSet resultset = statement.executeQuery("SELECT * FROM FORNECEDOR WHERE idFornecedor = '" + idFornecedor + "'");
            if (resultset.next()) {
            	if(resultset.getInt("tipo") == 1){
            		fornecedor = new FornecedorPessoaFisica(resultset.getInt("idFornecedor"));
            	}else{
            		fornecedor = new FornecedorPessoaJuridica(resultset.getInt("idFornecedor"));
            	}
            } else {
            	throw new FornecedorNaoEncontradoException(idFornecedor);
            }
			resultset.close();
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
        } catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		return fornecedor;
	}

	public void atualizar(Fornecedor fornecedor) throws FornecedorNaoEncontradoException, RepositorioException {
	    try {
    	    Statement statement = (Statement) pm.getCommunicationChannel();
    	    instanceEndereco.atualizar(fornecedor.getEndereco());
            int i = statement.executeUpdate("UPDATE FORNECEDOR SET " +
            		" nome = " + fornecedor.getNome() +
            		" email = " + fornecedor.getEmail() +
            		" telefone = " + fornecedor.getTelefone() +
           	        " WHERE idFornecedor = " + fornecedor.getIdFornecedor() + "");
            if (i == 0) {
            	throw new FornecedorNaoEncontradoException(fornecedor.getIdFornecedor());
            }
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
		} catch (SQLException e) {
		    throw new RepositorioException(e);
	    } finally {
	    	try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
	}

	public boolean existe(int idFornecedor) throws RepositorioException {
        boolean resposta = false;
        try {
            Statement statement = (Statement) pm.getCommunicationChannel();
            ResultSet resultset = statement.executeQuery("SELECT * FROM FORNECEDOR WHERE idFornecedor = " + idFornecedor + "");
            resposta = resultset.next();
			resultset.close();
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
        } catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
        return resposta;
    }

    public RepositorioFornecedores getContas() throws RepositorioException {
    	RepositorioFornecedores resposta = new RepositorioFornecedoresArray(100);
        try {
            Statement statement = (Statement) pm.getCommunicationChannel();
            ResultSet resultset = statement.executeQuery("SELECT * FROM FORNECEDOR");
            while (resultset.next()) {
            	Fornecedor fornecedor = null;
            	if(resultset.getInt("tipo") == 1){
            		fornecedor = new FornecedorPessoaFisica(resultset.getInt("idFornecedor"));
            	}else{
            		fornecedor = new FornecedorPessoaJuridica(resultset.getInt("idFornecedor"));
            	}
                resposta.inserir(fornecedor);
            }
			resultset.close();
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
        } catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
        return resposta;
    }

    public IteratorFornecedores getIterator() throws RepositorioException {
        return this.getIterator();
    }

	@Override
	public List<Fornecedor> getFornecedores() throws RepositorioException {
		
		RepositorioFornecedores resposta = new RepositorioFornecedoresArray(100);
		try {
			Statement statement = (Statement) pm.getCommunicationChannel();
			ResultSet resultset = statement.executeQuery("SELECT FORNECEDOR.*, endereco.*  FROM FORNECEDOR inner join endereco on endereco.idEndereco = fornecedor.idEndereco order by idLivro desc");
			while (resultset.next()) {
				Fornecedor fornecedor = null;
				Endereco endereco = null;
				endereco = new Endereco(resultset.getInt("idEndereco"), resultset.getString("logradouro"), resultset.getString("numero"), resultset.getString("bairro"), resultset.getString("cep"), resultset.getString("cidade"), resultset.getString("bairro"), resultset.getString("complemento"));
				fornecedor = new FornecedorPessoaFisica(resultset.getInt("idFornecedo"), endereco, resultset.getString("nome"), resultset.getString("email"), resultset.getString("telefone"), resultset.getString("cpf"));
				resposta.inserir(fornecedor);
			}
			resultset.close();
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		return resposta.getFornecedores();
	}

}